<?php
/**
 * Created by PhpStorm.
 * User: Sky
 * Date: 2017/8/7
 * Time: 下午5:12
 */

namespace app\models;


use app\librarys\DbCacheUtility;
use app\librarys\Tools;

class Group
{
    public static function getCount()
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_group t0 LEFT JOIN t_file t1 ON t1.id = t0.image_id');
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getList($page, $pageSize)
    {
        $cmd = \Yii::$app->db->createCommand('select t0.*,t1.url from t_group t0 LEFT JOIN t_file t1 ON t1.id = t0.image_id ORDER BY t0.create_datetime DESC limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    public static function getById($id)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_group where id=:id');
        $cmd->bindValue(':id', $id);
        $item = DbCacheUtility::queryOne($cmd);
        return $item;
    }

    public static function save($id, $name, $image_id, $remark)
    {
        $cmd = \Yii::$app->db->createCommand('
            insert into t_group(id, `name`, image_id, remark)
            values(:id, :name, :image_id, :remark)
            ON DUPLICATE KEY
            UPDATE `name`=:name, image_id=:image_id, remark=:remark;
        ');
        $cmd->bindValue(':id', $id == '0' ? Tools::db_uuid() : $id);
        $cmd->bindValue(':name', $name);
        $cmd->bindValue(':image_id', $image_id);
        $cmd->bindValue(':remark', $remark);
        DbCacheUtility::execute($cmd);
    }


    public static function getGroupMemberCountByGroupId($groupId)
    {
        $cmd = \Yii::$app->db->createCommand('
            select sum(c) from
            (select count(1) as c from t_group_merchant_relation t0
            LEFT join t_merchant t1 on t1.id=t0.merchant_id
            where t0.group_id=:groupId and t1.is_deleted=0 and t1.is_enabled=1
            UNION ALL
            select count(1) as c from t_group_user_relation where group_id=:groupId) as tt');
        $cmd->bindValue(':groupId', $groupId);
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getGroupMemberList($page, $pageSize, $groupId)
    {
        $cmd = \Yii::$app->db->createCommand('
           (select t0.group_id, t0.merchant_id as member_id, t0.create_datetime, t1.`name`, t1.phone, 1 as member_type from t_group_merchant_relation t0
            LEFT join t_merchant t1 on t1.id=t0.merchant_id
            WHERE t0.group_id=:groupId and t1.is_deleted=0 and t1.is_enabled=1)
            UNION
            (select t10.group_id, t10.user_id as member_id, t10.create_datetime, t11.`name`, t11.phone, 2 as member_type from t_group_user_relation t10
            LEFT join t_user t11 on t11.id=t10.user_id
            WHERE t10.group_id=:groupId)
            order by create_datetime DESC
             limit :offset , :limit');
        $cmd->bindValue(':groupId', $groupId);
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    /**
     * 根据user_id,group_id 获取申请记录
     * @param $user_id
     * @param $group_id
     * @return mixed
     */
    public static function getGroupUserAuth($user_id, $group_id)
    {
//        $sql = 'SELECT * FROM t_group_user_auth_record WHERE user_id = :user_id AND group_id = :group_id';
//        $cmd = \Yii::$app->db->createCommand($sql);
//        $cmd->bindValue(':user_id', $user_id);
//        $cmd->bindValue(':group_id', $group_id);
//
//        $item = DbCacheUtility::queryOne($cmd);
//        return $item;

        $sql = 'delete from t_group_user_auth_record WHERE user_id = :user_id AND group_id = :group_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $user_id);
        $cmd->bindValue(':group_id', $group_id);

        DbCacheUtility::execute($cmd);
    }

    /**
     * 申请(返回申请记录的id)
     * @param $user_id
     * @param $user_name
     * @param $user_phone
     * @param $group_id
     * @return false|null|string
     */
    public static function saveGroupUserAuth($user_id, $user_name, $user_phone, $group_id)
    {
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            self::getGroupUserAuth($user_id, $group_id);
            $id = Tools::db_uuid();
            $sql = 'insert into t_group_user_auth_record(id,group_id,user_id,name,phone,status,remark)
                values(:id,:group_id,:user_id,:name,:phone,:status,:remark)';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':group_id', $group_id);
            $cmd->bindValue(':user_id', $user_id);
            $cmd->bindValue(':name', $user_name);
            $cmd->bindValue(':phone', $user_phone);
            $cmd->bindValue(':status', 0);
            $cmd->bindValue(':remark', '');

            $result = DbCacheUtility::execute($cmd);

            if ($result > 0) {
                //自动审核通过
                $sql = 'update t_group_user_auth_record set status = 1 where id = :id';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':id', $id);
                $result = DbCacheUtility::execute($cmd);
                if ($result > 0) {
                    $sql = 'delete from t_group_user_relation where group_id = :group_id and user_id = :user_id';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':group_id', $group_id);
                    $cmd->bindValue(':user_id', $user_id);
                    DbCacheUtility::execute($cmd);

                    $sql = 'insert into t_group_user_relation(group_id,user_id) VALUES (:group_id,:user_id)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':group_id', $group_id);
                    $cmd->bindValue(':user_id', $user_id);
                    DbCacheUtility::execute($cmd);
                }

            }
            $transaction->commit();
            return $id;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return null;
        }

        return null;
    }

    public static function getAll()
    {
        $sql = 'SELECT id,`name` FROM t_group';
        $cmd = \Yii::$app->db->createCommand($sql);

        $item = DbCacheUtility::queryAll($cmd);
        return $item;
    }
}
